var express = require('express');
const sqlQuery = require('../../model/HGmysql');
const jiami = require('../../model/jiami');
//验证码模块
const getCaptcha = require('../../model/yzm');
const jwt = require('jsonwebtoken');
const gly = require('../../model/gly');
const ptgl = require('../../model/ptgl');
const shy = require('../../model/shy');


var router = express.Router();

/* GET home page. */
router.post('/login', async(req, res, next) => {
    let strSql = "select * from user where username=? and password = ?";
    let strSq2 = "select * from user where username=?";
    let strSq3 = "select * from user where state=true and username = ?"
    let strSq5 = "select * from user"
    let arr = [req.body.username, req.body.password];
    let result = await sqlQuery(strSql, arr);
    let result2 = await sqlQuery(strSq2, arr);
    let result3 = await sqlQuery(strSq3, [req.body.username]);
    let result5 = await sqlQuery(strSq5);
    console.log(req.body);
    console.log(req.session.cap);
    if (result2.length != 0) {
        if (result3.length != 0) {
            res.json({
                data: '该账号已登录,非本人操作请立即修改密码'
            })
        } else {
            if (req.body.yzm != req.session.cap) {
                return res.json({ data: '验证码错误(小写字母)' })
            }
            if (result.length != 0) {
                const token = jwt.sign(`${req.body.username}`, 'kk', )
                res.json({
                    data: '登录成功',
                    token,
                    result5
                })
                let strSq4 = `update user set state=true WHERE username='${req.body.username}'`
                let result4 = await sqlQuery(strSq4);
            } else {
                res.json({
                    data: '账号密码错误!'
                })
            }
        }
    } else {
        res.json({
            data: '用户名未注册!'
        })
    }

});
//图形验证码
router.get('/captcha', (req, res, next) => {
    getCaptcha(req, res, next)
});
//退出登录
router.get('/logouts', async(req, res) => {
    let username = req.query.username;
    let strSq1 = "select * from user where username=?";
    let arr = username;
    let result = await sqlQuery(strSq1, arr);
    if (result.length != 0) {
        res.json({
            data: '退出成功',
        })
        let strSq4 = `update user set state=false WHERE username='${req.query.username}'`
        let result4 = await sqlQuery(strSq4);
    } else {
        return res.json({
            data: '退出失败'
        })
    }
});
//获取菜单
router.get('/getmenu', async(req, res) => {
        let username = req.query.username
            //查询权限
        let strSql = `select jurisdiction from user where username='${username}'`;
        let strSql2 = `select * from user where username='${username}'`
        let result = await sqlQuery(strSql);
        let result2 = await sqlQuery(strSql2);
        let quantitys = `SELECT COUNT(stare) FROM  treviewed WHERE stare = '待审核'`;
        let quantitn = await sqlQuery(quantitys);
        let quantity = quantitn[0]['COUNT(stare)']
        result.forEach(element => {
            //console.log(element.jurisdiction);
            if (element.jurisdiction == '超级管理员') {
                return res.json({
                    data: gly,

                    result2,
                    meta: {
                        msg: '获取菜单列表成功',
                        status: 200
                    }
                })
            }
            if (element.jurisdiction == '审核员') {
                return res.json({
                    data: shy,
                    result2,
                    quantity,
                    meta: {
                        msg: '获取菜单列表成功',
                        status: 200
                    }
                })
            } else {
                return res.json({
                    data: ptgl,
                    result2,
                    meta: {
                        msg: '获取菜单列表成功',
                        status: 200
                    }
                })
            }
        });


    })
    //获取用户列表
router.get('/userlist', async(req, res) => {
        let pageSize = parseInt(req.query.pageSize)
        currentPage = parseInt(req.query.currentPage)
        let strSql = `SELECT * FROM user limit ?,?`;
        let arr = [(currentPage - 1) * pageSize, pageSize]
        let result = await sqlQuery(strSql, arr);
        //查询数据多少条
        let strSql2 = "select count(id) as username from user";
        let result1 = await sqlQuery(strSql2);
        let count = result1[0].username;
        return res.json({
            data: {
                result: result,

                count
            }
        })
    })
    //查询
router.get('/seluser', async(req, res) => {
        let pageSize = parseInt(req.query.pageSize)
        currentPage = parseInt(req.query.currentPage)
        let strSql = `SELECT*FROM user WHERE username LIKE '%${req.query.inputs}%' limit ?,?;`;
        let arr = [(currentPage - 1) * pageSize, pageSize]
        let result = await sqlQuery(strSql, arr);
        let strSq12 = "SELECT count(id) as username FROM user WHERE username LIKE '%" + req.query.inputs + "%';";
        let result2 = await sqlQuery(strSq12);
        let count = result2[0].username;
        return res.json({
            result,
            count
        })
    })
    //添加用户
router.post('/adduser', async(req, res) => {
        let { username, password, age, tel, sex, email, jurisdiction } = req.body
        let strSq2 = `select * from user where tel=${tel}`;
        let result2 = await sqlQuery(strSq2);
        let strSq3 = `select * from user where email='${email}'`;
        let result3 = await sqlQuery(strSq3);
        let strSq4 = `select * from user where username='${username}'`;
        let result4 = await sqlQuery(strSq4);
        if (result2 == 0)
            if (result3 == 0)
                if (result4 == 0) {
                    let strSql = "insert into user (username,password,age,sex,tel,email,jurisdiction) values(?,?,?,?,?,?,?)"
                    let result = await sqlQuery(strSql, [username, password, age, sex, tel, email, jurisdiction]);
                    return res.json({
                        status: 200,
                        data: '添加成功'
                    })
                } else {
                    return res.json({
                        status: 200,
                        data: '用户名已存在！'
                    })
                }
        else
            return res.json({
                status: 200,
                data: '邮箱已存在！'
            })
        else
            return res.json({
                status: 200,
                data: '手机号已存在！'
            })
    })
    //根据id获取用户
router.get('/userlist/:id', async(req, res) => {
        let strSql = `SELECT*FROM user WHERE id=?`;
        let arr = [req.params.id];
        let result = await sqlQuery(strSql, arr)

        for (const iterator of result) {
            return res.send({
                status: 200,
                data: iterator
            })
        }
    })
    //修改用户
router.put('/edit/:id', async(req, res) => {
        let { age, tel, sex, email, jurisdiction } = req.body
        let strSql = `update user set age=?,sex=?,tel=?,email=?,jurisdiction=? WHERE id=${req.params.id}`;
        let arr = [age, sex, tel, email, jurisdiction]
        let result = await sqlQuery(strSql, arr);
        return res.json({
            status: 200,
            result,
            data: '修改成功'
        })
    })
    //删除用户
router.delete('/userlist/:id', async(req, res, err) => {
        let { id } = req.params;
        let strSql = `delete from user where id = ${id}`;
        let result = await sqlQuery(strSql);
        if (err) {
            return res.json({
                status: 200,
                data: '删除成功'
            })

        } else {
            return res.json({
                data: '删除失败'
            })
        }
    })
    //添加用户地理位置
router.put('/xx/:user', async(req, res, err) => {
        let { lng, lat, dz } = req.body;
        let strSql = `update user set lng=?,lat=?,dz=? WHERE username='${req.params.user}'`;
        let arr = [lng, lat, dz]
        let result = await sqlQuery(strSql, arr);
        if (err) {
            return res.send('成功')
        } else {
            return res.send('失败')
        }
    })
    //获取年龄报表
router.get('/age', async(req, res, err) => {
    let strSql = 'select s.age,count(s.age) from user s GROUP BY age;';
    let result = await sqlQuery(strSql);
    if (err) {
        console.log(err);
        return res.json({
            status: 200,
            megess: '成功',
            data: result
        })
    } else {
        return res.json({
            megess: '失败'
        })
    }
})
router.get('/sex', async(req, res, err) => {
        let strSql = 'select count(*) as value,sex as name from user GROUP BY sex';
        let result = await sqlQuery(strSql);
        if (err) {

            return res.json({
                status: 200,
                megess: '成功',
                data: result
            })
        } else {
            return res.json({
                megess: '失败'
            })
        }
    })
    //头像上传
router.post('/uploader', async(req, res) => {
    //解析前台formdata
    var imgData = req.files.picFile
    username = req.body.username
    path = `public/upload/` + req.files.picFile.md5 + Date.now() + '.jpg'
    paths = `http://localhost:3000${path.slice(6)}`
    imgData.mv(path, async function(err) {
        if (err) {
            return res.status(500).send(err);

        } else {
            let strSql = `update user set tx='${paths}' where username = '${username}'`
            let result = await sqlQuery(strSql);
            return res.send({
                message: 'ok',
                file: paths
            });
        }
    });
})
router.put('/editpwd', async(req, res) => {
    let { tel, email, pass, username } = req.body
        //判断手机号
    let strSql = `SELECT * FROM user WHERE username='${username}' and tel='${tel}'`
    let result = await sqlQuery(strSql);
    //判断邮箱
    let strSql2 = `SELECT * FROM user WHERE username='${username}' and email='${email}'`
    let result2 = await sqlQuery(strSql2);
    if (result.length != 0) {
        if (result2.length != 0) {
            let strSql3 = `update user set password='${pass}' WHERE username='${username}'`;
            let result3 = await sqlQuery(strSql3);
            return res.send('修改成功');
        } else {
            return res.send('邮箱不匹配')
        }
    } else {
        return res.send('手机号不匹配')
    }
})
module.exports = router;